<?php
class Shop_Model_Tellist extends Shop_Model_Base{

	/**
	 * 店舗検索（左パネル）のキーワードから、店舗を取得
	 * @param unknown_type $params
	 */
	public function getSearchList($params)
	{
		$db_params = array();
		$sql = $this->generateSql($params, $db_params, true);
		return $this->dbGetList($sql, $db_params);
	}

	/**
	 * 店舗電話番号一覧データを表示用に変換
	 * @param unknown_type $telList
	 * @return 表示用データリスト
	 */
	public function convertData($telList, $No)
	{
		$DispList = array();
		$indexNo = 0;
		$beforePrefCd = null;
		$beforeCityCd = null;
		
		foreach ($telList as $telData)
		{
			$PrefCd = $telData['M05_TENPO_TODOUFUKEN_CD'];
			$CityCd = $telData['M05_TENPO_SHIKUCHOUSON_CD'];
			
			// 無料電話番号チェック
			$callData = Apm_ShopUtil::getCallingData($telData, Apm_Config_Shop::FREE_CONNECTION_NO_6);
			if ($callData['IS_FREE_CALL']) {
				// 無料電話
				if (!empty($No)) {
					$telNo = Apm_ShopUtil::getFreeCallingNo($telData, $No);
					if (empty($telNo)) $telNo = $callData['CONNECTION_NO'];
				} else {
					$telNo = $callData['CONNECTION_NO'];
				}
			} else {
				// 無料電話なし
				$telNo = null;
			}
			
			//店舗番号がNULLでなければ、一覧表示する
			if(!is_null($telNo)){
				
				// 都道府県が異なる場合
				if ($beforePrefCd != $PrefCd)
				{
					$DispList["$PrefCd"]['PrefName'] = $telData['M05_TENPO_TODOUFUKEN'];
					$beforeCityCd = null;
				}
				
				// 市区町村が異なる場合
				if ($beforeCityCd != $CityCd)
				{
					$DispList["$PrefCd"]['CityList'][] = array(
						'CityCd'	=> $CityCd,
						'CityName'  => $telData['M05_TENPO_SHIKUCHOUSON'],
						'TenpoList' => array()
					);
					$indexNo = count($DispList["$PrefCd"]['CityList']) - 1;
				}
				
				// リンク先URL
				$url = Apm_Bukkenurl::generateUrlByParametersShopDetail($telData) . '?btn=1';
				
				$DispList["$PrefCd"]['CityList'][$indexNo]['TenpoList'][] = array(
					'M05_HOUZIN_CD'          => $telData['M05_HOUZIN_CD'],
					'M05_TENPO_CD'           => $telData['M05_TENPO_CD'],
					'M05_TENPO_NAME'         => $telData['M05_TENPO_NAME'],
					'INFOMATION_CENTER_FLAG' => $telData['INFOMATION_CENTER_FLAG'],
					'M05_OEM'                => $telData['M05_OEM'],
					'M93_CONNECTION_NO6'     => $telNo,
					'SHOP_DTL_URL'           => $url
				);
				
				$beforePrefCd = $PrefCd;
				$beforeCityCd = $CityCd;
			}
		}
		
		return $DispList;
	}

    /**
     * 店舗電話番号一覧データ検索用SQL文の取得
     * 
     * 店舗電話番号一覧データ検索用SQL文の取得を行う
     * 
     * @param unknown_type $params
     * @param unknown_type $db_params
     * @param unknown_type $condFlg
     */
	protected function generateSql($params, &$db_params, $condFlg = false)
	{
		$condKeyword = '';

		if ($condFlg == true && (empty($params['keyword']) == false || $params['keyword'] == '0' ))
		{
			$condKeyword .= "AND (M05I.M05_TENPO_NAME LIKE '%{$params['keyword']}%'";
			$condKeyword .= "  OR UPPER(to_single_byte(M05I.M05_TENPO_NAME)) LIKE '%".strtoupper(mb_convert_kana($params['keyword'],'as','SJIS'))."%')";
		}
		
		// エリア店舗検索
		if (!empty($params['AreaCd']))
		{
			// 配列化
			$areaCd = array();
			if (!is_array($params['AreaCd'])) {
				$areaCd[] = $params['AreaCd'];
			} else {
				$areaCd   = $params['AreaCd'];
			}
			
			$i = "";
			$condKeyword .= " AND exists (";
			$condKeyword .= "    SELECT 1";
			$condKeyword .= "      FROM M01AREA M01";
			$condKeyword .= "     WHERE (";
			foreach ($areaCd as $key => $val) {
				$condKeyword .= $i."M01.M01_AREA_CD = :AreaCd_".$key;
				$db_params['AreaCd_'.$key] = $val;
				$i = " OR ";
			}
			$condKeyword .= "     ) ";
			$condKeyword .= "       AND M01.M01_TODOUFUKEN_CD = M05I.M05_TENPO_TODOUFUKEN_CD";
			$condKeyword .= " ) ";
		}
		
		// 都道府県店舗検索
		if (!empty($params['PrefCd']))
		{
			// 配列化
			$prefCd = array();
			if (!is_array($params['PrefCd'])) {
				$prefCd[] = $params['PrefCd'];
			} else {
				$prefCd   = $params['PrefCd'];
			}
			
			$i = "";
			$condKeyword .= " AND (";
			foreach ($prefCd as $key => $val) {
				$condKeyword .= $i."M05I.M05_TENPO_TODOUFUKEN_CD = :PrefCd_".$key;
				$db_params['PrefCd_'.$key] = $val;
				$i = " OR ";
			}
			$condKeyword .= " ) ";
		}
		
		// ダミー店舗除外
		$condKeyword .= $this->generateNotinDummyShop("M05I.M05_TENPO_CD", $db_params);

$sql = <<< docEnd
SELECT
        M05I.M05_HOUZIN_CD
        ,M05I.M05_TENPO_CD
        ,M05I.M05_TENPO_BANGOU
        ,M05I.M05_TENPO_TODOUFUKEN_CD
        ,M05I.M05_TENPO_TODOUFUKEN
        ,M05I.M05_TENPO_SHIKUCHOUSON_CD
        ,M05I.M05_TENPO_SHIKUCHOUSON
        ,M05I.M05_TENPO_NAME
        ,M05I.M05_OEM
        ,SUBSTR(M05S.M05_SERVICE_FLAG, 12, 1) AS INFOMATION_CENTER_FLAG
docEnd;
for ($call_no=1; $call_no<=Apm_Config_Shop::CALLNOTE_NO_MAX; $call_no++) {
	$sql .= " ,M93_CONNECTION_NO".$call_no;
	$sql .= " ,M93_CONTROL_FLAG".$call_no;
}
$sql .= <<< docEnd
        ,M93_USE_KBN
        ,M93_START_DATE
        ,M93_DEL_DATE
    FROM
        M05TENPO_INFO M05I
        ,M05TENPO_SERVICE M05S
        ,M93CALLNOTE M93
    WHERE
        M05I.M05_KOUKAI_FLAG = 1
        AND (
            M05I.M05_TENPO_CD > '01000000'
            AND M05I.M05_TENPO_CD < '48000000'
        )
        AND M05I.M05_TENPO_CD = M05S.M05_TENPO_CD
        AND M05I.M05_TENPO_CD = M93.M93_TENPO_CD
		AND M93.M93_USE_KBN = '1'
		AND M93.M93_START_DATE <= sysdate
		AND nvl(M93.M93_DEL_DATE, sysdate+1) > sysdate
        {$condKeyword}
    ORDER BY
        M05I.M05_TENPO_TODOUFUKEN_CD ASC
        ,M05I.M05_TENPO_SHIKUCHOUSON_CD ASC
        ,M05I.M05_TENPO_CD ASC
docEnd;

		return $sql;
	}
}
?>
